
package com.moyun.common.utils;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.moyun.model.common.R;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @ClassName ExcelUtils
 * @Description TODO 使用hutool工具类进行简单的excel导入和导出的工具类
 * @Author yangboxuan
 * @Date 2021/3/27 14:10
 */
@Slf4j
public class ExcelUtils {

    private static List<List<Object>> lineList = new ArrayList<>();

    /**
     * excel 导出工具类
     *
     * @param response
     * @param fileName    文件名
     * @param projects    对象集合
     * @param columnNames 导出的excel中的列名
     * @param keys        对应的是对象中的字段名字
     * @throws IOException
     */
    public static void export(HttpServletResponse response, String fileName, List<?> projects, String[] columnNames, String[] keys) {

        ExcelWriter bigWriter = ExcelUtil.getBigWriter();

        for (int i = 0; i < columnNames.length; i++) {
            bigWriter.addHeaderAlias(columnNames[i], keys[i]);
        }
//        // 合并单元格后的标题行，使用默认标题样式
//        bigWriter.merge(6, "学员学习记录");
        // 一次性写出内容，使用默认样式，强制输出标题
        bigWriter.write(projects, true);
        // 设置所有列为自动宽度，不考虑合并单元格
//        setSizeColumn(bigWriter.getSheet(),columnNames.length-1);
//        bigWriter.autoSizeColumnAll();
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        ServletOutputStream out = null;
        PrintWriter writer = null;
        try {
            writer = response.getWriter();
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
            out = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
            log.error("Excel导出出错{}", e.getMessage());
            R<?> jsonResult = R.error("excel导出出错,请联系管理员");
            writer.write(JSON.toJSONString(jsonResult));
        } finally {
            bigWriter.flush(out, true);
            // 关闭writer，释放内存
            bigWriter.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
            IoUtil.close(bigWriter);
            IoUtil.close(writer);
        }
    }


    @Test
    public void importExcel() {
//        // 简单验证一下是不是excel(用于网页中文件上传)
//        String type = ".xlsx";
//        String failMsg = "请上传合法Excel";
//        if (!(file.getOriginalFilename() != null && file.getOriginalFilename().endsWith(type))) {
//            return ;
//        }
        // 1. 读取excel文件字节流
        ExcelReader reader = ExcelUtil.getReader(ResourceUtil.getStream("C:\\Users\\Yangboxuan\\Desktop\\工作簿1.xlsx"));
        // 2. 将读取到的数据,批量转换为字符串类型
        List<List<Object>> rowListTemp = reader.read();
        if (rowListTemp == null||rowListTemp.size()==0) {
            // 实现自己的业务逻辑
            log.error("excel文件为空！！！");
            return;
        }
        List<List<String>> rowList = rowListTemp.stream().map(cellList -> cellList.stream().map(cell ->
        {
            String cellStr = Convert.convert(String.class, cell);
            return StrUtil.cleanBlank(cellStr);
        }).collect(Collectors.toList())).collect(Collectors.toList());


//        for (List<String> cellList : rowList) {
//            for (int i = 0; i < cellList.size(); i++) {
//                System.out.println(cellList.get(i));
//            }
//        }
//        System.out.println("hahahah");


    }



}
